OML4SQL Clustering OC.dsnb

[{"layout":null,"template":null,"templateConfig":null,"name":"OML4SQL Clustering OC","description":null,"readOnly":false,"type":"low","paragraphs":[{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":null,"message":[],"enabled":true,"result":{"startTime":1715350345488,"interpreter":"md.low","endTime":1715350345563,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","# Identifying Customer Segments using Orthogonal Partitioning Clustering","","Oracle Machine Learning supports clustering using several algorithms, including k-Means, O-Cluster, and Expectation Maximization. In this notebook, we illustrate how to identify natural clusters of customers using the CUSTOMERS dataset from the SH schema using the unsupervised learning k-Means algorithm. The data exploration, preparation, and machine learning runs inside Oracle Autonomous Database.","","See the documentation link below for details on the Orthogonal Partitioning (O-Cluster) in-database algortihm.","","Copyright (c) 2024 Oracle Corporation ","###### <a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a>","---"],"enabled":true,"result":{"startTime":1715350345638,"interpreter":"md.low","endTime":1715350345713,"results":[{"message":"<h1 id=\"identifying-customer-segments-using-orthogonal-partitioning-clustering\">Identifying Customer Segments using Orthogonal Partitioning Clustering<\/h1>\n<p>Oracle Machine Learning supports clustering using several algorithms, including k-Means, O-Cluster, and Expectation Maximization. In this notebook, we illustrate how to identify natural clusters of customers using the CUSTOMERS dataset from the SH schema using the unsupervised learning k-Means algorithm. The data exploration, preparation, and machine learning runs inside Oracle Autonomous Database.<\/p>\n<p>See the documentation link below for details on the Orthogonal Partitioning (O-Cluster) in-database algortihm.<\/p>\n<p>Copyright (c) 2024 Oracle Corporation<\/p>\n<h6 id=\"the-universal-permissive-license-upl-version-10\"><a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a><\/h6>\n<hr />\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":9,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","![tiny arrow](http://www.oracle.com/technetwork/database/options/advanced-analytics/clustering-5663171.jpg \"tiny arrow\")"],"enabled":true,"result":{"startTime":1715350345794,"interpreter":"md.low","endTime":1715350345870,"results":[{"message":"<p><img src=\"http://www.oracle.com/technetwork/database/options/advanced-analytics/clustering-5663171.jpg\" alt=\"tiny arrow\" title=\"tiny arrow\" /><\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":3,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"For more information...","message":["%md ","","* <a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a>","* <a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a>","* <a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a>","* <a href=\"https://www.oracle.com/goto/ml-clustering\" target=\"_blank\">OML Clustering<\/a>","* <a href=\"https://oracle.com/goto/ml-o-cluster\" target=\"_blank\">OML Orthogonal Partitioning<\/a>"],"enabled":true,"result":{"startTime":1715350345951,"interpreter":"md.low","endTime":1715350346014,"results":[{"message":"<ul>\n<li><a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a><\/li>\n<li><a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a><\/li>\n<li><a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a><\/li>\n<li><a href=\"https://www.oracle.com/goto/ml-clustering\" target=\"_blank\">OML Clustering<\/a><\/li>\n<li><a href=\"https://oracle.com/goto/ml-o-cluster\" target=\"_blank\">OML Orthogonal Partitioning<\/a><\/li>\n<\/ul>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create view joining CUSTOMERS and DEMOGRAPHICS","message":["%script","","CREATE OR REPLACE VIEW CUSTOMERS360_V AS"," SELECT a.CUST_ID, a.CUST_GENDER, a.CUST_MARITAL_STATUS, "," a.CUST_YEAR_OF_BIRTH, a.CUST_INCOME_LEVEL, a.CUST_CREDIT_LIMIT, "," b.EDUCATION, b.AFFINITY_CARD, "," b.HOUSEHOLD_SIZE, b.OCCUPATION, b.YRS_RESIDENCE, b.Y_BOX_GAMES"," FROM SH.CUSTOMERS a, SH.SUPPLEMENTARY_DEMOGRAPHICS b"," WHERE a.CUST_ID = b.CUST_ID;"," "],"enabled":true,"result":{"startTime":1715350346096,"interpreter":"script.low","endTime":1715350348093,"results":[{"message":"\nView CUSTOMERS360_V created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Count number of records in CUSTOMERS360_V","message":["%sql","","SELECT COUNT(*) FROM CUSTOMERS360_V;"],"enabled":true,"result":{"startTime":1715350348178,"interpreter":"sql.low","endTime":1715350348328,"results":[{"message":"COUNT(*)\n4500\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display data in view CUSTOMERS360_V","message":["%sql","","SELECT * ","FROM CUSTOMERS360_V","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715350348404,"interpreter":"sql.low","endTime":1715350348506,"results":[{"message":"CUST_ID\tCUST_GENDER\tCUST_MARITAL_STATUS\tCUST_YEAR_OF_BIRTH\tCUST_INCOME_LEVEL\tCUST_CREDIT_LIMIT\tEDUCATION\tAFFINITY_CARD\tHOUSEHOLD_SIZE\tOCCUPATION\tYRS_RESIDENCE\tY_BOX_GAMES\n100134\tF\tDivorc.\t1965\tL: 300,000 and above\t9000\tAssoc-A\t0\t2\tCleric.\t2\t0\n102828\tF\tNeverM\t1967\tE: 90,000 - 109,999\t10000\tHS-grad\t0\t1\tMachine\t4\t0\n101232\tM\tNeverM\t1979\tJ: 190,000 - 249,999\t9000\t< Bach.\t0\t1\tOther\t2\t1\n100696\tM\tMarried\t1971\tF: 110,000 - 129,999\t7000\tProfsc\t1\t3\tProf.\t3\t0\n103948\tM\tNeverM\t1966\tJ: 190,000 - 249,999\t9000\t< Bach.\t0\t1\tCleric.\t4\t0\n103791\tM\tDivorc.\t1952\tB: 30,000 - 49,999\t3000\tHS-grad\t0\t2\tProf.\t5\t0\n100804\tF\tDivorc.\t1943\tA: Below 30,000\t1500\t< Bach.\t0\t2\tProf.\t6\t0\n101610\tM\tNeverM\t1985\tI: 170,000 - 189,999\t3000\t11th\t0\t1\tHandler\t0\t1\n102308\tM\tNeverM\t1980\tJ: 190,000 - 249,999\t11000\t< Bach.\t0\t2\tProf.\t2\t1\n100593\tM\tMarried\t1963\tG: 130,000 - 149,999\t1500\tHS-grad\t1\t3\tProf.\t4\t0\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Build an O-Cluster model using the CUSTOMERS360_V view"],"enabled":true,"result":{"startTime":1715350348585,"interpreter":"md.low","endTime":1715350348649,"results":[{"message":"<h3 id=\"build-an-o-cluster-model-using-the-customers360_v-view\">Build an O-Cluster model using the CUSTOMERS360_V view<\/h3>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build the OC Clustering model with default settings","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('OC_SH_CLUS_SAMPLE');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;","BEGIN"," v_setlst('ALGO_NAME') := 'ALGO_O_CLUSTER';"," V_setlst('PREP_AUTO') := 'ON';",""," DBMS_DATA_MINING.CREATE_MODEL2("," MODEL_NAME => 'OC_SH_CLUS_SAMPLE',"," MINING_FUNCTION => 'CLUSTERING',"," DATA_QUERY => 'select * from CUSTOMERS360_V',"," SET_LIST => v_setlst,"," CASE_ID_COLUMN_NAME => 'CUST_ID');","END;"],"enabled":true,"result":{"startTime":1715350348728,"interpreter":"script.low","endTime":1715350353329,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Examples of possible setting overrides for Orthogonal Partitioning Cluster clustering","","If the user does not override the default settings, then relevant settings are determined by the algorithm.","","A complete list of settings can be found in the Documentation link:","","* <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-12C659A2-0317-47C8-A05D-708FAF7DF370\" target=\"_blank\">Algorithm Settings<\/a>","* <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" target=\"_blank\">Shared Settings<\/a>","","* Specify a row weight column ","> v_setlst('ODMS_ROW_WEIGHT_COLUMN_NAME') := '<row_weight_column_name>'; ","* Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is `ODMS_MISSING_VALUE_AUTO`. The option `ODMS_MISSING_VALUE_MEAN_MODE` replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option `ODMS_MISSING_VALUE_AUTO` performs different strategies for different algorithms. When `ODMS_MISSING_VALUE_TREATMENT` is set to `ODMS_MISSING_VALUE_DELETE_ROW`, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.","> v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';","* Specify the maximum number of leaf clusters generated by a clustering algorithm. The algorithm may return fewer clusters, depending on the data. Enhanced k-Means usually produces the exact number of clusters specified by CLUS_NUM_CLUSTERS, unless there are fewer distinct data points. For k-Means and O-Cluster, the default is 10. ","> v_setlst('CLUS_NUM_CLUSTERS') := '5';","* Specify a fraction that specifies the peak density required for separating a new cluster. The fraction is related to the global uniform density. It requires a number between 0 and 1 (inclusive). The default is 0.5. ","v_setlst('OCLT_SENSITIVITY') := '0.5';"],"enabled":true,"result":{"startTime":1715350353415,"interpreter":"md.low","endTime":1715350353483,"results":[{"message":"<h3 id=\"examples-of-possible-setting-overrides-for-orthogonal-partitioning-cluster-clustering\">Examples of possible setting overrides for Orthogonal Partitioning Cluster clustering<\/h3>\n<p>If the user does not override the default settings, then relevant settings are determined by the algorithm.<\/p>\n<p>A complete list of settings can be found in the Documentation link:<\/p>\n<ul>\n<li>\n<p><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-12C659A2-0317-47C8-A05D-708FAF7DF370\" target=\"_blank\">Algorithm Settings<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" target=\"_blank\">Shared Settings<\/a><\/p>\n<\/li>\n<li>\n<p>Specify a row weight column<\/p>\n<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_ROW_WEIGHT_COLUMN_NAME') := '&lt;row_weight_column_name&gt;';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is <code>ODMS_MISSING_VALUE_AUTO<\/code>. The option <code>ODMS_MISSING_VALUE_MEAN_MODE<\/code> replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option <code>ODMS_MISSING_VALUE_AUTO<\/code> performs different strategies for different algorithms. When <code>ODMS_MISSING_VALUE_TREATMENT<\/code> is set to <code>ODMS_MISSING_VALUE_DELETE_ROW<\/code>, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the maximum number of leaf clusters generated by a clustering algorithm. The algorithm may return fewer clusters, depending on the data. Enhanced k-Means usually produces the exact number of clusters specified by CLUS_NUM_CLUSTERS, unless there are fewer distinct data points. For k-Means and O-Cluster, the default is 10.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('CLUS_NUM_CLUSTERS') := '5';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify a fraction that specifies the peak density required for separating a new cluster. The fraction is related to the global uniform density. It requires a number between 0 and 1 (inclusive). The default is 0.5.\nv_setlst('OCLT_SENSITIVITY') := '0.5';<\/li>\n<\/ul>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build the OC Clustering model with explicit settings","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('OC_SH_CLUS_SAMPLE');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;","BEGIN"," v_setlst('ALGO_NAME') := 'ALGO_O_CLUSTER';"," V_setlst('PREP_AUTO') := 'ON';"," V_setlst('CLUS_NUM_CLUSTERS') := '5';"," V_setlst('OCLT_SENSITIVITY') := '0.5';",""," DBMS_DATA_MINING.CREATE_MODEL2("," MODEL_NAME => 'OC_SH_CLUS_SAMPLE',"," MINING_FUNCTION => 'CLUSTERING',"," DATA_QUERY => 'select * from CUSTOMERS360_V',"," SET_LIST => v_setlst,"," CASE_ID_COLUMN_NAME => 'CUST_ID');","END;"],"enabled":true,"result":{"startTime":1715350353563,"interpreter":"script.low","endTime":1715350356165,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the model metadata","message":["%sql ","","SELECT MINING_FUNCTION, ALGORITHM","FROM USER_MINING_MODELS","WHERE MODEL_NAME = 'OC_SH_CLUS_SAMPLE';"],"enabled":true,"result":{"startTime":1715350356242,"interpreter":"sql.low","endTime":1715350356316,"results":[{"message":"MINING_FUNCTION\tALGORITHM\nCLUSTERING\tO_CLUSTER\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the model signature","message":["%sql ","","-- The model signature includes the attributes used in model training data,","-- and also corresponds to the attributes expected to apply the model to new data. ","","SELECT ATTRIBUTE_NAME, ATTRIBUTE_TYPE","FROM USER_MINING_MODEL_ATTRIBUTES","WHERE MODEL_NAME = 'OC_SH_CLUS_SAMPLE'","ORDER BY ATTRIBUTE_NAME;"],"enabled":true,"result":{"startTime":1715350356395,"interpreter":"sql.low","endTime":1715350356471,"results":[{"message":"ATTRIBUTE_NAME\tATTRIBUTE_TYPE\nAFFINITY_CARD\tNUMERICAL\nCUST_CREDIT_LIMIT\tNUMERICAL\nCUST_GENDER\tCATEGORICAL\nCUST_INCOME_LEVEL\tCATEGORICAL\nCUST_MARITAL_STATUS\tCATEGORICAL\nCUST_YEAR_OF_BIRTH\tNUMERICAL\nEDUCATION\tCATEGORICAL\nHOUSEHOLD_SIZE\tCATEGORICAL\nOCCUPATION\tCATEGORICAL\nYRS_RESIDENCE\tNUMERICAL\nY_BOX_GAMES\tNUMERICAL\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the actual settings used by the algorithm","message":["%sql","","SELECT SETTING_NAME, SETTING_VALUE","FROM USER_MINING_MODEL_SETTINGS","WHERE MODEL_NAME = 'OC_SH_CLUS_SAMPLE'","ORDER BY SETTING_NAME;"],"enabled":true,"result":{"startTime":1715350356548,"interpreter":"sql.low","endTime":1715350356621,"results":[{"message":"SETTING_NAME\tSETTING_VALUE\nALGO_NAME\tALGO_O_CLUSTER\nCLUS_NUM_CLUSTERS\t5\nOCLT_SENSITIVITY\t0.5\nODMS_DETAILS\tODMS_ENABLE\nODMS_MISSING_VALUE_TREATMENT\tODMS_MISSING_VALUE_AUTO\nODMS_SAMPLING\tODMS_SAMPLING_DISABLE\nPREP_AUTO\tON\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the O-Cluster model details","message":["%sql ","","-- Display the list of available cluster model details as indicated in VIEW_TYPE","-- Cluster details are presented as separate views depending on the desired information.","","SELECT VIEW_NAME, VIEW_TYPE FROM USER_MINING_MODEL_VIEWS","WHERE MODEL_NAME='OC_SH_CLUS_SAMPLE'","ORDER BY VIEW_NAME;"],"enabled":true,"result":{"startTime":1715350356697,"interpreter":"sql.low","endTime":1715350356821,"results":[{"message":"VIEW_NAME\tVIEW_TYPE\nDM$VAOC_SH_CLUS_SAMPLE\tClustering Attribute Statistics\nDM$VBOC_SH_CLUS_SAMPLE\tAutomatic Data Preparation Binning\nDM$VDOC_SH_CLUS_SAMPLE\tClustering Description\nDM$VGOC_SH_CLUS_SAMPLE\tGlobal Name-Value Pairs\nDM$VHOC_SH_CLUS_SAMPLE\tClustering Histograms\nDM$VROC_SH_CLUS_SAMPLE\tClustering Rules\nDM$VSOC_SH_CLUS_SAMPLE\tComputed Settings\nDM$VWOC_SH_CLUS_SAMPLE\tModel Build Alerts\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Show Cluster details","message":["%sql","","-- For each cluster_id, this DM$VD* view provides the number of records ","-- in the cluster, the parent cluster id, the level in the hierarchy. ","","SELECT CLUSTER_ID, RECORD_COUNT, PARENT, TREE_LEVEL","FROM DM$VDOC_SH_CLUS_SAMPLE","ORDER BY CLUSTER_ID;"],"enabled":true,"result":{"startTime":1715350356903,"interpreter":"sql.low","endTime":1715350357060,"results":[{"message":"CLUSTER_ID\tRECORD_COUNT\tPARENT\tTREE_LEVEL\n1\t4500\t\t1\n2\t2316\t1\t2\n3\t2184\t1\t2\n4\t1690\t3\t3\n5\t494\t3\t3\n6\t991\t4\t4\n7\t699\t4\t4\n8\t993\t2\t3\n9\t1323\t2\t3\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Show leaf clusters IDs","message":["%sql","","-- Show leaf IDs to know for which clusters to show detail","","SELECT CLUSTER_ID","FROM DM$VDOC_SH_CLUS_SAMPLE","WHERE LEFT_CHILD_ID IS NULL AND RIGHT_CHILD_ID IS NULL","ORDER BY CLUSTER_ID"],"enabled":true,"result":{"startTime":1715350357142,"interpreter":"sql.low","endTime":1715350357236,"results":[{"message":"CLUSTER_ID\n5\n6\n7\n8\n9\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Show Cluster details for cluster 9","message":["%sql","","-- For cluster_id 9, this output lists all the attributes that constitute the centroid, with the mean (for numericals) ","-- or mode (for categoricals). It also includes the variance.","","SELECT CLUSTER_ID, ATTRIBUTE_NAME, round(MEAN,3) MEAN, round(VARIANCE,3) VARIANCE, MODE_VALUE","FROM DM$VAOC_SH_CLUS_SAMPLE","WHERE CLUSTER_ID = 9","ORDER BY ATTRIBUTE_NAME, ATTRIBUTE_SUBNAME;"],"enabled":true,"result":{"startTime":1715350357312,"interpreter":"sql.low","endTime":1715350357406,"results":[{"message":"CLUSTER_ID\tATTRIBUTE_NAME\tMEAN\tVARIANCE\tMODE_VALUE\n9\tAFFINITY_CARD\t0.4\t0.24\t\n9\tCUST_CREDIT_LIMIT\t6938.398\t3213108.474\t\n9\tCUST_GENDER\t\t\tM\n9\tCUST_INCOME_LEVEL\t\t\tG: 130,000 - 149,999\n9\tCUST_MARITAL_STATUS\t\t\tMarried\n9\tCUST_YEAR_OF_BIRTH\t1963.322\t181.359\t\n9\tEDUCATION\t\t\tHS-grad\n9\tHOUSEHOLD_SIZE\t\t\t3\n9\tOCCUPATION\t\t\tSales\n9\tYRS_RESIDENCE\t4.203\t3.811\t\n9\tY_BOX_GAMES\t0.252\t0.189\t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":8,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","In the following example, the view shows attribute level details for the rule associated with each cluster id. ","For an attribute, support indicates the number of records that fall within the attribute range specified in the rule antecedent where the given attribute is not null. ","Confidence is a number between 0 and 1 that indicates how relevant this attribute is in distinguishing the records in the cluster from all the records in the whole data.","The larger the number, the more relevant the attribute."," ","The query below reverse-transforms the data to its original values, since the data used for building the model was normalized."],"enabled":true,"result":{"startTime":1715350357486,"interpreter":"md.low","endTime":1715350357544,"results":[{"message":"<p>In the following example, the view shows attribute level details for the rule associated with each cluster id.\nFor an attribute, support indicates the number of records that fall within the attribute range specified in the rule antecedent where the given attribute is not null.\nConfidence is a number between 0 and 1 that indicates how relevant this attribute is in distinguishing the records in the cluster from all the records in the whole data.\nThe larger the number, the more relevant the attribute.<\/p>\n<p>The query below reverse-transforms the data to its original values, since the data used for building the model was normalized.<\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display rule details for leaf clusters","message":["%sql","","SELECT CLUSTER_ID, ATTRIBUTE_NAME, OPERATOR,"," NUMERIC_VALUE, ATTRIBUTE_VALUE, SUPPORT, ROUND(CONFIDENCE,3) CONFIDENCE","FROM DM$VROC_SH_CLUS_SAMPLE ","WHERE cluster_id IN (SELECT cluster_id"," FROM DM$VDOC_SH_CLUS_SAMPLE"," WHERE LEFT_CHILD_ID is NULL "," AND RIGHT_CHILD_ID is NULL)","ORDER BY CLUSTER_ID, ATTRIBUTE_NAME, ATTRIBUTE_SUBNAME, OPERATOR, NUMERIC_VALUE, ATTRIBUTE_VALUE;"],"enabled":true,"result":{"startTime":1715350357622,"interpreter":"sql.low","endTime":1715350357806,"results":[{"message":"CLUSTER_ID\tATTRIBUTE_NAME\tOPERATOR\tNUMERIC_VALUE\tATTRIBUTE_VALUE\tSUPPORT\tCONFIDENCE\n5\tAFFINITY_CARD\t<=\t\t1\t494\t0.001\n5\tAFFINITY_CARD\t>=\t\t0\t494\t0.001\n5\tCUST_CREDIT_LIMIT\t=\t\t(14035.7; 15000]\t469\t0.166\n5\tCUST_GENDER\tIN\t\tF\t494\t0.001\n5\tCUST_GENDER\tIN\t\tM\t494\t0.001\n5\tCUST_INCOME_LEVEL\tIN\t\tH: 150,000 - 169,999\t444\t0.055\n5\tCUST_INCOME_LEVEL\tIN\t\tJ: 190,000 - 249,999\t444\t0.055\n5\tCUST_INCOME_LEVEL\tIN\t\tK: 250,000 - 299,999\t444\t0.055\n5\tCUST_INCOME_LEVEL\tIN\t\tL: 300,000 and above\t444\t0.055\n5\tCUST_MARITAL_STATUS\tIN\t\tDivorc.\t458\t0\n5\tCUST_MARITAL_STATUS\tIN\t\tMarried\t458\t0\n5\tCUST_MARITAL_STATUS\tIN\t\tNeverM\t458\t0\n5\tCUST_YEAR_OF_BIRTH\t<=\t\t1986\t468\t0.003\n5\tCUST_YEAR_OF_BIRTH\t>=\t\t1942.86\t468\t0.003\n5\tEDUCATION\tIN\t\t< Bach.\t412\t0.004\n5\tEDUCATION\tIN\t\tAssoc-V\t412\t0.004\n5\tEDUCATION\tIN\t\tBach.\t412\t0.004\n5\tEDUCATION\tIN\t\tHS-grad\t412\t0.004\n5\tEDUCATION\tIN\t\tMasters\t412\t0.004\n5\tEDUCATION\tIN\t\tProfsc\t412\t0.004\n5\tHOUSEHOLD_SIZE\tIN\t\t1\t411\t0.001\n5\tHOUSEHOLD_SIZE\tIN\t\t2\t411\t0.001\n5\tHOUSEHOLD_SIZE\tIN\t\t3\t411\t0.001\n5\tOCCUPATION\tIN\t\t?\t485\t0.002\n5\tOCCUPATION\tIN\t\tCleric.\t485\t0.002\n5\tOCCUPATION\tIN\t\tCrafts\t485\t0.002\n5\tOCCUPATION\tIN\t\tExec.\t485\t0.002\n5\tOCCUPATION\tIN\t\tHandler\t485\t0.002\n5\tOCCUPATION\tIN\t\tMachine\t485\t0.002\n5\tOCCUPATION\tIN\t\tOther\t485\t0.002\n5\tOCCUPATION\tIN\t\tProf.\t485\t0.002\n5\tOCCUPATION\tIN\t\tProtec.\t485\t0.002\n5\tOCCUPATION\tIN\t\tSales\t485\t0.002\n5\tOCCUPATION\tIN\t\tTechSup\t485\t0.002\n5\tOCCUPATION\tIN\t\tTransp.\t485\t0.002\n5\tYRS_RESIDENCE\t<=\t\t7.46667\t478\t0.001\n5\tYRS_RESIDENCE\t>=\t\t.933333\t478\t0.001\n5\tY_BOX_GAMES\t<=\t\t1\t494\t0.001\n5\tY_BOX_GAMES\t>=\t\t0\t494\t0.001\n6\tAFFINITY_CARD\t=\t\t[0; .5]\t991\t0.131\n6\tCUST_CREDIT_LIMIT\t<=\t\t10178.6\t969\t0.12\n6\tCUST_CREDIT_LIMIT\t>=\t\t8250\t969\t0.12\n6\tCUST_GENDER\tIN\t\tF\t991\t0.003\n6\tCUST_GENDER\tIN\t\tM\t991\t0.003\n6\tCUST_INCOME_LEVEL\tIN\t\tE: 90,000 - 109,999\t960\t0.017\n6\tCUST_INCOME_LEVEL\tIN\t\tF: 110,000 - 129,999\t960\t0.017\n6\tCUST_INCOME_LEVEL\tIN\t\tG: 130,000 - 149,999\t960\t0.017\n6\tCUST_INCOME_LEVEL\tIN\t\tH: 150,000 - 169,999\t960\t0.017\n6\tCUST_INCOME_LEVEL\tIN\t\tI: 170,000 - 189,999\t960\t0.017\n6\tCUST_INCOME_LEVEL\tIN\t\tJ: 190,000 - 249,999\t960\t0.017\n6\tCUST_INCOME_LEVEL\tIN\t\tK: 250,000 - 299,999\t960\t0.017\n6\tCUST_INCOME_LEVEL\tIN\t\tL: 300,000 and above\t960\t0.017\n6\tCUST_MARITAL_STATUS\tIN\t\tDivorc.\t891\t0.006\n6\tCUST_MARITAL_STATUS\tIN\t\tMarried\t891\t0.006\n6\tCUST_MARITAL_STATUS\tIN\t\tNeverM\t891\t0.006\n6\tCUST_YEAR_OF_BIRTH\t<=\t\t1986\t934\t0.002\n6\tCUST_YEAR_OF_BIRTH\t>=\t\t1942.86\t934\t0.002\n6\tEDUCATION\tIN\t\t10th\t870\t0.006\n6\tEDUCATION\tIN\t\t< Bach.\t870\t0.006\n6\tEDUCATION\tIN\t\tAssoc-A\t870\t0.006\n6\tEDUCATION\tIN\t\tAssoc-V\t870\t0.006\n6\tEDUCATION\tIN\t\tBach.\t870\t0.006\n6\tEDUCATION\tIN\t\tHS-grad\t870\t0.006\n6\tHOUSEHOLD_SIZE\tIN\t\t1\t913\t0.007\n6\tHOUSEHOLD_SIZE\tIN\t\t2\t913\t0.007\n6\tHOUSEHOLD_SIZE\tIN\t\t3\t913\t0.007\n6\tHOUSEHOLD_SIZE\tIN\t\t9+\t913\t0.007\n6\tOCCUPATION\tIN\t\t?\t910\t0.002\n6\tOCCUPATION\tIN\t\tCleric.\t910\t0.002\n6\tOCCUPATION\tIN\t\tCrafts\t910\t0.002\n6\tOCCUPATION\tIN\t\tExec.\t910\t0.002\n6\tOCCUPATION\tIN\t\tHandler\t910\t0.002\n6\tOCCUPATION\tIN\t\tMachine\t910\t0.002\n6\tOCCUPATION\tIN\t\tOther\t910\t0.002\n6\tOCCUPATION\tIN\t\tProf.\t910\t0.002\n6\tOCCUPATION\tIN\t\tSales\t910\t0.002\n6\tOCCUPATION\tIN\t\tTransp.\t910\t0.002\n6\tYRS_RESIDENCE\t<=\t\t6.53333\t918\t0.002\n6\tYRS_RESIDENCE\t>=\t\t.933333\t918\t0.002\n6\tY_BOX_GAMES\t<=\t\t1\t991\t0.004\n6\tY_BOX_GAMES\t>=\t\t0\t991\t0.004\n7\tAFFINITY_CARD\t=\t\t[0; .5]\t562\t0.002\n7\tCUST_CREDIT_LIMIT\t=\t\t(10178.6; 11142.9]\t699\t0.172\n7\tCUST_GENDER\tIN\t\tF\t699\t0.001\n7\tCUST_GENDER\tIN\t\tM\t699\t0.001\n7\tCUST_INCOME_LEVEL\tIN\t\tI: 170,000 - 189,999\t695\t0.077\n7\tCUST_INCOME_LEVEL\tIN\t\tJ: 190,000 - 249,999\t695\t0.077\n7\tCUST_INCOME_LEVEL\tIN\t\tK: 250,000 - 299,999\t695\t0.077\n7\tCUST_INCOME_LEVEL\tIN\t\tL: 300,000 and above\t695\t0.077\n7\tCUST_MARITAL_STATUS\tIN\t\tDivorc.\t645\t0.001\n7\tCUST_MARITAL_STATUS\tIN\t\tMarried\t645\t0.001\n7\tCUST_MARITAL_STATUS\tIN\t\tNeverM\t645\t0.001\n7\tCUST_YEAR_OF_BIRTH\t<=\t\t1986\t656\t0.002\n7\tCUST_YEAR_OF_BIRTH\t>=\t\t1942.86\t656\t0.002\n7\tEDUCATION\tIN\t\t10th\t635\t0.002\n7\tEDUCATION\tIN\t\t11th\t635\t0.002\n7\tEDUCATION\tIN\t\t< Bach.\t635\t0.002\n7\tEDUCATION\tIN\t\tAssoc-A\t635\t0.002\n7\tEDUCATION\tIN\t\tAssoc-V\t635\t0.002\n7\tEDUCATION\tIN\t\tBach.\t635\t0.002\n7\tEDUCATION\tIN\t\tHS-grad\t635\t0.002\n7\tEDUCATION\tIN\t\tMasters\t635\t0.002\n7\tHOUSEHOLD_SIZE\tIN\t\t1\t643\t0.001\n7\tHOUSEHOLD_SIZE\tIN\t\t2\t643\t0.001\n7\tHOUSEHOLD_SIZE\tIN\t\t3\t643\t0.001\n7\tHOUSEHOLD_SIZE\tIN\t\t9+\t643\t0.001\n7\tOCCUPATION\tIN\t\t?\t649\t0.001\n7\tOCCUPATION\tIN\t\tCleric.\t649\t0.001\n7\tOCCUPATION\tIN\t\tCrafts\t649\t0.001\n7\tOCCUPATION\tIN\t\tExec.\t649\t0.001\n7\tOCCUPATION\tIN\t\tHandler\t649\t0.001\n7\tOCCUPATION\tIN\t\tMachine\t649\t0.001\n7\tOCCUPATION\tIN\t\tOther\t649\t0.001\n7\tOCCUPATION\tIN\t\tProf.\t649\t0.001\n7\tOCCUPATION\tIN\t\tSales\t649\t0.001\n7\tOCCUPATION\tIN\t\tTransp.\t649\t0.001\n7\tYRS_RESIDENCE\t<=\t\t7.46667\t666\t0.001\n7\tYRS_RESIDENCE\t>=\t\t.933333\t666\t0.001\n7\tY_BOX_GAMES\t<=\t\t1\t699\t0.002\n7\tY_BOX_GAMES\t>=\t\t0\t699\t0.002\n8\tAFFINITY_CARD\t<=\t\t1\t993\t0.002\n8\tAFFINITY_CARD\t>=\t\t0\t993\t0.002\n8\tCUST_CREDIT_LIMIT\t<=\t\t3428.57\t977\t0.141\n8\tCUST_CREDIT_LIMIT\t>=\t\t1500\t977\t0.141\n8\tCUST_GENDER\tIN\t\tF\t993\t0\n8\tCUST_GENDER\tIN\t\tM\t993\t0\n8\tCUST_INCOME_LEVEL\tIN\t\tA: Below 30,000\t993\t0.08\n8\tCUST_INCOME_LEVEL\tIN\t\tB: 30,000 - 49,999\t993\t0.08\n8\tCUST_INCOME_LEVEL\tIN\t\tC: 50,000 - 69,999\t993\t0.08\n8\tCUST_INCOME_LEVEL\tIN\t\tD: 70,000 - 89,999\t993\t0.08\n8\tCUST_INCOME_LEVEL\tIN\t\tE: 90,000 - 109,999\t993\t0.08\n8\tCUST_INCOME_LEVEL\tIN\t\tF: 110,000 - 129,999\t993\t0.08\n8\tCUST_INCOME_LEVEL\tIN\t\tG: 130,000 - 149,999\t993\t0.08\n8\tCUST_INCOME_LEVEL\tIN\t\tH: 150,000 - 169,999\t993\t0.08\n8\tCUST_INCOME_LEVEL\tIN\t\tI: 170,000 - 189,999\t993\t0.08\n8\tCUST_MARITAL_STATUS\tIN\t\tDivorc.\t914\t0.001\n8\tCUST_MARITAL_STATUS\tIN\t\tMarried\t914\t0.001\n8\tCUST_MARITAL_STATUS\tIN\t\tNeverM\t914\t0.001\n8\tCUST_YEAR_OF_BIRTH\t<=\t\t1986\t934\t0.001\n8\tCUST_YEAR_OF_BIRTH\t>=\t\t1939.55\t934\t0.001\n8\tEDUCATION\tIN\t\t< Bach.\t886\t0.003\n8\tEDUCATION\tIN\t\tAssoc-A\t886\t0.003\n8\tEDUCATION\tIN\t\tAssoc-V\t886\t0.003\n8\tEDUCATION\tIN\t\tBach.\t886\t0.003\n8\tEDUCATION\tIN\t\tHS-grad\t886\t0.003\n8\tEDUCATION\tIN\t\tMasters\t886\t0.003\n8\tHOUSEHOLD_SIZE\tIN\t\t1\t916\t0\n8\tHOUSEHOLD_SIZE\tIN\t\t2\t916\t0\n8\tHOUSEHOLD_SIZE\tIN\t\t3\t916\t0\n8\tHOUSEHOLD_SIZE\tIN\t\t9+\t916\t0\n8\tOCCUPATION\tIN\t\t?\t939\t0.001\n8\tOCCUPATION\tIN\t\tCleric.\t939\t0.001\n8\tOCCUPATION\tIN\t\tCrafts\t939\t0.001\n8\tOCCUPATION\tIN\t\tExec.\t939\t0.001\n8\tOCCUPATION\tIN\t\tFarming\t939\t0.001\n8\tOCCUPATION\tIN\t\tMachine\t939\t0.001\n8\tOCCUPATION\tIN\t\tOther\t939\t0.001\n8\tOCCUPATION\tIN\t\tProf.\t939\t0.001\n8\tOCCUPATION\tIN\t\tSales\t939\t0.001\n8\tOCCUPATION\tIN\t\tTechSup\t939\t0.001\n8\tOCCUPATION\tIN\t\tTransp.\t939\t0.001\n8\tYRS_RESIDENCE\t<=\t\t7.46667\t930\t0.001\n8\tYRS_RESIDENCE\t>=\t\t.933333\t930\t0.001\n8\tY_BOX_GAMES\t<=\t\t1\t993\t0.002\n8\tY_BOX_GAMES\t>=\t\t0\t993\t0.002\n9\tAFFINITY_CARD\t<=\t\t1\t1323\t0.022\n9\tAFFINITY_CARD\t>=\t\t0\t1323\t0.022\n9\tCUST_CREDIT_LIMIT\t<=\t\t10178.6\t1293\t0.079\n9\tCUST_CREDIT_LIMIT\t>=\t\t4392.86\t1293\t0.079\n9\tCUST_GENDER\tIN\t\tF\t1323\t0.001\n9\tCUST_GENDER\tIN\t\tM\t1323\t0.001\n9\tCUST_INCOME_LEVEL\tIN\t\tC: 50,000 - 69,999\t1224\t0.011\n9\tCUST_INCOME_LEVEL\tIN\t\tE: 90,000 - 109,999\t1224\t0.011\n9\tCUST_INCOME_LEVEL\tIN\t\tF: 110,000 - 129,999\t1224\t0.011\n9\tCUST_INCOME_LEVEL\tIN\t\tG: 130,000 - 149,999\t1224\t0.011\n9\tCUST_INCOME_LEVEL\tIN\t\tH: 150,000 - 169,999\t1224\t0.011\n9\tCUST_INCOME_LEVEL\tIN\t\tI: 170,000 - 189,999\t1224\t0.011\n9\tCUST_INCOME_LEVEL\tIN\t\tJ: 190,000 - 249,999\t1224\t0.011\n9\tCUST_INCOME_LEVEL\tIN\t\tK: 250,000 - 299,999\t1224\t0.011\n9\tCUST_INCOME_LEVEL\tIN\t\tL: 300,000 and above\t1224\t0.011\n9\tCUST_MARITAL_STATUS\tIN\t\tDivorc.\t1244\t0.003\n9\tCUST_MARITAL_STATUS\tIN\t\tMarried\t1244\t0.003\n9\tCUST_MARITAL_STATUS\tIN\t\tNeverM\t1244\t0.003\n9\tCUST_YEAR_OF_BIRTH\t<=\t\t1986\t1223\t0.001\n9\tCUST_YEAR_OF_BIRTH\t>=\t\t1942.86\t1223\t0.001\n9\tEDUCATION\tIN\t\t10th\t1215\t0.002\n9\tEDUCATION\tIN\t\t11th\t1215\t0.002\n9\tEDUCATION\tIN\t\t< Bach.\t1215\t0.002\n9\tEDUCATION\tIN\t\tAssoc-A\t1215\t0.002\n9\tEDUCATION\tIN\t\tAssoc-V\t1215\t0.002\n9\tEDUCATION\tIN\t\tBach.\t1215\t0.002\n9\tEDUCATION\tIN\t\tHS-grad\t1215\t0.002\n9\tEDUCATION\tIN\t\tMasters\t1215\t0.002\n9\tHOUSEHOLD_SIZE\tIN\t\t1\t1209\t0.003\n9\tHOUSEHOLD_SIZE\tIN\t\t2\t1209\t0.003\n9\tHOUSEHOLD_SIZE\tIN\t\t3\t1209\t0.003\n9\tHOUSEHOLD_SIZE\tIN\t\t9+\t1209\t0.003\n9\tOCCUPATION\tIN\t\t?\t1231\t0.001\n9\tOCCUPATION\tIN\t\tCleric.\t1231\t0.001\n9\tOCCUPATION\tIN\t\tCrafts\t1231\t0.001\n9\tOCCUPATION\tIN\t\tExec.\t1231\t0.001\n9\tOCCUPATION\tIN\t\tMachine\t1231\t0.001\n9\tOCCUPATION\tIN\t\tOther\t1231\t0.001\n9\tOCCUPATION\tIN\t\tProf.\t1231\t0.001\n9\tOCCUPATION\tIN\t\tSales\t1231\t0.001\n9\tOCCUPATION\tIN\t\tTechSup\t1231\t0.001\n9\tOCCUPATION\tIN\t\tTransp.\t1231\t0.001\n9\tYRS_RESIDENCE\t<=\t\t7.46667\t1236\t0.001\n9\tYRS_RESIDENCE\t>=\t\t.933333\t1236\t0.001\n9\tY_BOX_GAMES\t<=\t\t1\t1323\t0.003\n9\tY_BOX_GAMES\t>=\t\t0\t1323\t0.003\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"bar\":{\"showSeries\":[\"COUNT\"],\"series\":{\"availableSeriesElements\":[{\"id\":\"COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"CLUS\",\"COUNT\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Apply model to CUSTOMERS360_V and display customer count per cluster","message":["%sql","","-- For a descriptive machine learning technique like Clustering, \"scoring\" involves assigning each record to a cluster, with a certain probability. ","-- However, one can also obtain the probability of a record belonging to each cluster.","-- Hover over bars in the bar chart to view the size of each cluster. ","","SELECT CLUSTER_ID(OC_SH_CLUS_SAMPLE USING *) AS CLUS, COUNT(*) AS COUNT ","FROM CUSTOMERS360_V","GROUP BY CLUSTER_ID(OC_SH_CLUS_SAMPLE USING *)","ORDER BY COUNT DESC;"],"enabled":true,"result":{"startTime":1715350357888,"interpreter":"sql.low","endTime":1715350357985,"results":[{"message":"CLUS\tCOUNT\n9\t1323\n8\t993\n6\t991\n7\t699\n5\t494\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"List ten customers most likely in cluster 6","message":["%sql","","SELECT CUST_ID, "," ROUND(CLUSTER_PROBABILITY(OC_SH_CLUS_SAMPLE, 6 USING *),4) PROB","FROM CUSTOMERS360_V","ORDER BY PROB DESC","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715350358062,"interpreter":"sql.low","endTime":1715350358183,"results":[{"message":"CUST_ID\tPROB\n104171\t0.9999\n103255\t0.9999\n100885\t0.9999\n101929\t0.9999\n100105\t0.9999\n103786\t0.9999\n104082\t0.9999\n104400\t0.9999\n100432\t0.9999\n104470\t0.9999\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":5,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"List four most relevant attributes for specific customers and likely cluster assignments","message":["%sql","","-- For customer id, pick among 101362, 102087 or 100456. These represent customers ","-- with > 20% likelihood of assignment to their cluster.","","SELECT CUST_ID,"," CLUSTER_ID,"," ROUND(PROB*100,2) PROB_PCT,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute1\",17,100)),'rank=\"1\"/>') FIRST_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute2\",17,100)),'rank=\"2\"/>') SECOND_ATTRIBUTE","FROM (SELECT CUST_ID, S.CLUSTER_ID, PROBABILITY PROB, "," CLUSTER_DETAILS(OC_SH_CLUS_SAMPLE USING T.*) DETAIL"," FROM (SELECT V.*, CLUSTER_SET(OC_SH_CLUS_SAMPLE, NULL, 0.2 USING *) PSET"," FROM CUSTOMERS360_V V"," WHERE cust_id = ${CUST_ID ='101362','101362'|'102087'| '100456'}) T,"," TABLE(T.PSET) S"," ORDER BY 2 DESC) OUT,"," XMLTABLE('/Details'"," PASSING OUT.DETAIL"," COLUMNS "," \"Attribute1\" XMLType PATH 'Attribute[1]',"," \"Attribute2\" XMLType PATH 'Attribute[2]') OUTPRED;"],"enabled":true,"result":{"startTime":1715350358261,"interpreter":"sql.low","endTime":1715350358530,"results":[{"message":"CUST_ID\tCLUSTER_ID\tPROB_PCT\tFIRST_ATTRIBUTE\tSECOND_ATTRIBUTE\n101362\t6\t98.43\t\"CUST_INCOME_LEVEL\" actualValue=\"H: 150,000 - 169,999\" weight=\".983\" \t\"CUST_CREDIT_LIMIT\" actualValue=\"11000\" operator=\"between\" range=\"(1.018E+004:1.114E+004]\" weight=\".\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":7,"hideResult":false,"dynamicFormParams":"{\"CUST_ID\":\"'101362'\",\"cluster_id\":\"'5'\",\"s.cluster_id\":\"'5'\",\"cust_id\":\"'100456'\"}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[{\"type\":\"Select\",\"name\":\"CUST_ID\",\"displayName\":null,\"defaultValue\":\"'101362'\",\"argument\":null,\"options\":[{\"value\":\"'101362'\",\"displayName\":null},{\"value\":\"'102087'\",\"displayName\":null},{\"value\":\"'100456'\",\"displayName\":null}],\"isHidden\":false,\"isProgrammatic\":false}]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## Alternative approach for building clustering model using an analytic function","In addition to creating a persistent model that is stored as a schema object, models can be built and scored on data dynamically using Oracle's analytic function syntax.","","In the following example, we segment customers into 4 groups based on common characteristics and provide the segment assignments. Note that this query does not reference a pre-build clustering model, but rather it segments the input data on the fly. Rerunning the same query with different input will result in a different segmentation. It also provides the main reasons (attributes) why a given customer is placed into a specific cluster.","","Note that the where clause has to be placed outside of the inline view so that the analytic function will build the clustering model on all the data, and not just the selected customers."],"enabled":true,"result":{"startTime":1715350358607,"interpreter":"md.low","endTime":1715350358667,"results":[{"message":"<h2 id=\"alternative-approach-for-building-clustering-model-using-an-analytic-function\">Alternative approach for building clustering model using an analytic function<\/h2>\n<p>In addition to creating a persistent model that is stored as a schema object, models can be built and scored on data dynamically using Oracle's analytic function syntax.<\/p>\n<p>In the following example, we segment customers into 4 groups based on common characteristics and provide the segment assignments. Note that this query does not reference a pre-build clustering model, but rather it segments the input data on the fly. Rerunning the same query with different input will result in a different segmentation. It also provides the main reasons (attributes) why a given customer is placed into a specific cluster.<\/p>\n<p>Note that the where clause has to be placed outside of the inline view so that the analytic function will build the clustering model on all the data, and not just the selected customers.<\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Build and apply a transient model using analytic functions","message":["%sql","","SELECT CUST_ID,"," CLUSTER_ID,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute1\",17,100)),'rank=\"1\"/>') FIRST_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute2\",17,100)),'rank=\"2\"/>') SECOND_ATTRIBUTE","FROM (SELECT * "," FROM (SELECT CUST_ID,"," CLUSTER_ID(INTO 4 USING CUST_GENDER, CUST_MARITAL_STATUS, CUST_YEAR_OF_BIRTH, CUST_INCOME_LEVEL, "," CUST_CREDIT_LIMIT, EDUCATION, AFFINITY_CARD, HOUSEHOLD_SIZE, OCCUPATION,"," YRS_RESIDENCE, Y_BOX_GAMES) OVER () CLUSTER_ID,"," CLUSTER_DETAILS(INTO 4 USING CUST_GENDER, CUST_MARITAL_STATUS, CUST_YEAR_OF_BIRTH, CUST_INCOME_LEVEL, "," CUST_CREDIT_LIMIT, EDUCATION, AFFINITY_CARD, HOUSEHOLD_SIZE, OCCUPATION,"," YRS_RESIDENCE, Y_BOX_GAMES) OVER () CLUSTER_DET"," FROM CUSTOMERS360_V)"," WHERE CUST_ID <= 100010"," ORDER By 1) OUT,"," XMLTABLE('/Details'"," PASSING OUT.CLUSTER_DET"," COLUMNS "," \"Attribute1\" XMLType PATH 'Attribute[1]',"," \"Attribute2\" XMLType PATH 'Attribute[2]') OUTPRED;"," "],"enabled":true,"result":{"startTime":1715350358745,"interpreter":"sql.low","endTime":1715350359015,"results":[{"message":"CUST_ID\tCLUSTER_ID\tFIRST_ATTRIBUTE\tSECOND_ATTRIBUTE\n100001\t3\t\"CUST_YEAR_OF_BIRTH\" actualValue=\"1941\" weight=\".059\" \t\"CUST_CREDIT_LIMIT\" actualValue=\"1500\" weight=\".038\" \n100002\t4\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".025\" \t\"CUST_GENDER\" actualValue=\"F\" weight=\".024\" \n100003\t4\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".025\" \t\"CUST_MARITAL_STATUS\" actualValue=\"NeverM\" weight=\".018\" \n100004\t4\t\"CUST_CREDIT_LIMIT\" actualValue=\"15000\" weight=\".038\" \t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".028\" \n100005\t1\t\"AFFINITY_CARD\" actualValue=\"1\" weight=\".157\" \t\"Y_BOX_GAMES\" actualValue=\"0\" weight=\".009\" \n100006\t2\t\"Y_BOX_GAMES\" actualValue=\"1\" weight=\".131\" \t\"CUST_YEAR_OF_BIRTH\" actualValue=\"1983\" weight=\".086\" \n100007\t4\t\"CUST_MARITAL_STATUS\" actualValue=\"Divorc.\" weight=\".027\" \t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".026\" \n100008\t4\t\"CUST_CREDIT_LIMIT\" actualValue=\"15000\" weight=\".035\" \t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".026\" \n100009\t1\t\"AFFINITY_CARD\" actualValue=\"1\" weight=\".137\" \t\"EDUCATION\" actualValue=\"Bach.\" weight=\".01\" \n100010\t2\t\"Y_BOX_GAMES\" actualValue=\"1\" weight=\".115\" \t\"CUST_YEAR_OF_BIRTH\" actualValue=\"1975\" weight=\".046\" \n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## Alternative approach to creating clustering model using a settings table","","The settings table is an alternative way to specify algorithm settings to build the model. We first drop the table, create it, and then populate it with settings before building the model. ","","NOTE: The settings table can be reused or modified in multiple model build executions. "],"enabled":true,"result":{"startTime":1715350359092,"interpreter":"md.low","endTime":1715350359158,"results":[{"message":"<h2 id=\"alternative-approach-to-creating-clustering-model-using-a-settings-table\">Alternative approach to creating clustering model using a settings table<\/h2>\n<p>The settings table is an alternative way to specify algorithm settings to build the model. We first drop the table, create it, and then populate it with settings before building the model.<\/p>\n<p>NOTE: The settings table can be reused or modified in multiple model build executions.<\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create and populate model settings table","message":["%script ","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE OC_SH_SAMPLE_SETTINGS PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","CREATE TABLE OC_SH_SAMPLE_SETTINGS (SETTING_NAME VARCHAR2(30),"," SETTING_VALUE VARCHAR2(4000));","/","BEGIN "," INSERT INTO OC_SH_SAMPLE_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.OCLT_SENSITIVITY,'0.5');"," INSERT INTO OC_SH_SAMPLE_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.PREP_AUTO,DBMS_DATA_MINING.PREP_AUTO_ON);","END;"],"enabled":true,"result":{"startTime":1715350359236,"interpreter":"script.low","endTime":1715350359420,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nTable OC_SH_SAMPLE_SETTINGS created.\n\n\n---------------------------\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"View the contents of the model settings table","message":["%sql","","SELECT * FROM OC_SH_SAMPLE_SETTINGS;"],"enabled":true,"result":{"startTime":1715350359494,"interpreter":"sql.low","endTime":1715350359587,"results":[{"message":"SETTING_NAME\tSETTING_VALUE\nOCLT_SENSITIVITY\t0.5\nPREP_AUTO\tON\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Drop, then build the k-Means Clustering model","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('OC_SH_CLUS_SAMPLE2');","EXCEPTION WHEN OTHERS THEN NULL; END;","","BEGIN","DBMS_DATA_MINING.CREATE_MODEL("," MODEL_NAME => 'OC_SH_CLUS_SAMPLE2',"," MINING_FUNCTION => DBMS_DATA_MINING.CLUSTERING,"," DATA_TABLE_NAME => 'CUSTOMERS360_V',"," CASE_ID_COLUMN_NAME => 'CUST_ID',"," SETTINGS_TABLE_NAME => 'OC_SH_SAMPLE_SETTINGS');","END;"],"enabled":true,"result":{"startTime":1715350359664,"interpreter":"script.low","endTime":1715350362166,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the actual settings used by the algorithm","message":["%sql","","SELECT SETTING_NAME, SETTING_VALUE","FROM USER_MINING_MODEL_SETTINGS","WHERE MODEL_NAME = 'OC_SH_CLUS_SAMPLE2'","ORDER BY SETTING_NAME;"],"enabled":true,"result":{"startTime":1715350362251,"interpreter":"sql.low","endTime":1715350362328,"results":[{"message":"SETTING_NAME\tSETTING_VALUE\nALGO_NAME\tALGO_KMEANS\nCLUS_NUM_CLUSTERS\t10\nKMNS_CONV_TOLERANCE\t.001\nKMNS_DETAILS\tKMNS_DETAILS_HIERARCHY\nKMNS_DISTANCE\tKMNS_EUCLIDEAN\nKMNS_ITERATIONS\t20\nKMNS_MIN_PCT_ATTR_SUPPORT\t.1\nKMNS_NUM_BINS\t11\nKMNS_RANDOM_SEED\t0\nKMNS_SPLIT_CRITERION\tKMNS_VARIANCE\nOCLT_SENSITIVITY\t0.5\nODMS_DETAILS\tODMS_ENABLE\nODMS_MISSING_VALUE_TREATMENT\tODMS_MISSING_VALUE_AUTO\nODMS_SAMPLING\tODMS_SAMPLING_DISABLE\nPREP_AUTO\tON\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## End of Script"],"enabled":true,"result":{"startTime":1715350362405,"interpreter":"md.low","endTime":1715350362464,"results":[{"message":"<h2 id=\"end-of-script\">End of Script<\/h2>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md"],"enabled":true,"result":{"startTime":1715350362543,"interpreter":"md.low","endTime":1715350362607,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"}],"version":"6","snapshot":false,"tags":null}]